package bdddaosimpl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import bdd.beans.*;
import bdddaos.*;
import java.util.Set;

public class DAONoteImpl implements DAONote {
	private DBLink dblink;

    public DAONoteImpl() {
		this.dblink = new DBLink();
    }

	public Note save(Note o) {

		try {
			dblink.connexion();
			this.dblink.setPst(this.dblink.getConnection().prepareStatement("INSERT INTO NOTE (NUMETUDIANT, NUMEXAMEN, NOTE)" +
					" VALUES ('"+o.getEtudiant().getNumetudiant()+"', '"+o.getExamen().getNumexamen()+"','"+o.getNote()+"',)"));
			this.dblink.getPst().executeQuery();
		}
		catch(SQLException se) {
			System.out.println("Erreur de requ�te");
		}
		finally{
			dblink.deconnexion();
		}
		return o;
	}

	public Note find(Integer NumEtudiant, Integer NumExamen) {
		Note o = new Note();
		try {
			dblink.connexion();
			this.dblink.setPst(this.dblink.getConnection().prepareStatement("SELECT * FROM NOTE WHERE NUMETUDIANT = " + NumEtudiant + " AND NUMEXAMEN = " + NumExamen));
			ResultSet rs = this.dblink.getPst().executeQuery();
			while(rs.next()) {
				o.setNote(rs.getDouble("NOTE"));
			}
		}
		catch(SQLException se) {
			System.out.println("Erreur de requête");
		}
		finally{
			dblink.deconnexion();
		}
		return o;
	}

	public List<Note> findAll() {
		List<Note> Lstretour = new ArrayList<Note>();
		try {
			this.dblink.connexion();
			this.dblink.setPst(this.dblink.getConnection().prepareStatement("SELECT * FROM NOTE"));
			ResultSet rs = this.dblink.getPst().executeQuery();
			while(rs.next()) {
				Note retour = new Note();
				retour.setEtudiant(new DAOEtudiantImpl().find(rs.getInt("NUMETUDIANT")));
				retour.setExamen(new DAOExamenImpl().find(rs.getInt("NUMEXAMEN")));
				retour.setNote(rs.getDouble("NOTE"));
				Lstretour.add(retour);
			}
		}
		catch(SQLException se) {
			System.out.println("Erreur de requête" + se);
		}
		finally{
			this.dblink.deconnexion();
		}
		return Lstretour;
	}

	@Override
	public void update(Note o) {
		try {
			this.dblink.connexion();
			String r = "UPDATE NOTE SET NOTE=" +o.getNote()+ " WHERE NUMETUDIANT = " + o.getEtudiant().getNumetudiant() + " AND NUMEXAMEN = " + o.getExamen().getNumexamen();

			this.dblink.setPst(this.dblink.getConnection().prepareStatement(r));
			this.dblink.getPst().executeQuery();
		}
		catch(SQLException se) {
			System.out.println("Erreur de requ�te : " + se);
		}
		finally{
			this.dblink.deconnexion();
		}
	}

	@Override
	public void delete(Note o) {
		try {
			this.dblink.connexion();
			String s = "DELETE FROM NOTE WHERE NUMNOTE=" + o.getId();

			this.dblink.setPst(this.dblink.getConnection().prepareStatement(s));
			this.dblink.getPst().executeQuery();
		}
		catch(SQLException se) {
			System.out.println("Erreur de requete : " + se);
		}
		finally{
			this.dblink.deconnexion();
		}
	}

	public List<Note> findByEcEnseignantPromotion(Integer numEtudiant, Integer numEnseignant, Integer promotion, Integer ec, Integer numeroExamen) {
		List<Note> liste = new ArrayList();
		dblink.connexion();
		
		String query = "select NOTE.NUMETUDIANT, NOTE.NOTE, NOTE.NUMEXAMEN, EXAMEN.LIBELLE "
                    + "from NOTE,ENSEIGNANT,PROMOTION,EC,JNTTYPEECENSEIGNANT,JNTETUDIANTPROMOTION, Examen "
                    + "where EC.NUMEC=JNTTYPEECENSEIGNANT.NUMEC AND "
                    + "JNTTYPEECENSEIGNANT.NUMENSEIGNANT=ENSEIGNANT.NUMENSEIGNANT AND "
                    + "NOTE.NUMETUDIANT=JNTETUDIANTPROMOTION.NUMETUDIANT AND "
                    + "PROMOTION.NUMPROMOTION=JNTETUDIANTPROMOTION.NUMPROMOTION "
                    + " and note.numexamen=examen.numexamen "
                    + " and JNTETUDIANTPROMOTION.NUMETUDIANT=" + numEtudiant
                    + " and Enseignant.numenseignant=" + numEnseignant
                    + " and promotion.numPromotion=" + promotion
                    + " and EC.numEC=" + ec
                    + " and note.numexamen=" + numeroExamen
                    + " order by note.numetudiant, note.numexamen";
		
		try {
			PreparedStatement pst = dblink.getConnection().prepareStatement(query); 
			ResultSet rs = pst.executeQuery();
			
			while(rs.next()) {
				Note n = new Note();
				n.setNote(rs.getDouble("NOTE"));
				DAOEtudiant daoEtudiant = new DAOEtudiantImpl();
				n.setEtudiant(daoEtudiant.find(rs.getInt("NUMETUDIANT")));
				DAOExamen daoExamen = new DAOExamenImpl();
				n.setExamen(daoExamen.find(rs.getInt("NUMEXAMEN")));
				
				liste.add(n);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			dblink.deconnexion();
		}
		
		return liste;
	}

    public List<Note> findByEtudiant(Integer numEtudiant) {
        List<Note> liste = new ArrayList<Note>();
        dblink.connexion();

        String query = "select * from Note where numetudiant = "+numEtudiant;

        try {
                PreparedStatement pst = dblink.getConnection().prepareStatement(query);
                ResultSet rs = pst.executeQuery();

                while(rs.next()) {
                        Note c = new Note();

                        c.setEtudiant(new DAOEtudiantImpl().find(rs.getInt("NUMETUDIANT")));
                        c.setExamen(new DAOExamenImpl().find(rs.getInt("NUMEXAMEN")));
                        c.setNote(rs.getDouble("NOTE"));
                        liste.add(c);
                }
        } catch (SQLException e) {
                e.printStackTrace();
        }finally {
                dblink.deconnexion();
        }
        return liste;
    }

    public Note find(Integer id) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public List<Note> findByExamen(Integer numExamen) {
        List<Note> liste = new ArrayList<Note>();
        dblink.connexion();

        String query = "select * from Note where numexamen = "+numExamen;

        try {
                PreparedStatement pst = dblink.getConnection().prepareStatement(query);
                ResultSet rs = pst.executeQuery();

                while(rs.next()) {
                        Note c = new Note();

                        c.setEtudiant(new DAOEtudiantImpl().find(rs.getInt("NUMETUDIANT")));
                        c.setExamen(new DAOExamenImpl().find(rs.getInt("NUMEXAMEN")));
                        c.setNote(rs.getDouble("NOTE"));
                        liste.add(c);
                }
        } catch (SQLException e) {
                e.printStackTrace();
        }finally {
                dblink.deconnexion();
        }
        return liste;
    }

    public List<Note> findByEtudiantExamen(Integer numEtudiant, Integer numExamen) {
        List<Note> liste = new ArrayList<Note>();
        dblink.connexion();

        String query = "select * from Note where numetudiant = "+numEtudiant + " and numexamen = "+numExamen;

        try {
                PreparedStatement pst = dblink.getConnection().prepareStatement(query);
                ResultSet rs = pst.executeQuery();

                while(rs.next()) {
                        Note c = new Note();

                        c.setEtudiant(new DAOEtudiantImpl().find(rs.getInt("NUMETUDIANT")));
                        c.setExamen(new DAOExamenImpl().find(rs.getInt("NUMEXAMEN")));
                        c.setNote(rs.getDouble("NOTE"));
                        liste.add(c);
                }
        } catch (SQLException e) {
                e.printStackTrace();
        }finally {
                dblink.deconnexion();
        }
        return liste;
    }
}
